﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class EssentialInformationService
    {
      
        public int HaveTab(string TabNam) {
            string sql = string.Format("select count(1) from EssentialInformation{0} ", TabNam);
            try {
                SqlConnection conn = ConnPoll.GetConn();
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
            } catch (Exception) {
                return -1;
            }
        }

        //public bool SearchTable(string UserName)
        //{
        //    SqlConnection conn = ConnPoll.GetConn();
        //    bool tempbool = false;
        //    string sql = "select * from EssentialInformation"+ UserName;
        //    try
        //    {
        //        ConnPoll.Open();
        //        SqlCommand com = new SqlCommand(sql, conn);
        //        SqlDataReader dr = com.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            tempbool = true;
        //            break;
        //        }
        //        dr.Close();
        //        com = null;
        //        ConnPoll.Close();
        //    }
        //    catch (Exception)
        //    {
        //        tempbool = false;
        //    }
        //    return tempbool;
        //}

        /// <summary>
        /// 建基本信息表
        /// </summary>
        /// <param name="UserName"></param>
        public void CreateTable(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string Name = "EssentialInformation" + UserName;
            try
            {
                string sql = "use bds249611391_db"
                    + " create table " + Name
                    + " ("
                    + " EssentialInformationId int not null identity(1,1),"
                    + " RemarksName nvarchar(90) null,"
                    + " NickName nvarchar(90) null,"
                    + " AccountNumber nvarchar(20) not null,"
                    + " The nvarchar(30) not null,"
                    + " Sex nvarchar(4) not null,"
                    + " Old nvarchar(20) null,"
                    + " DateOfBirth datetime not null,"
                    + " Telephone nvarchar(12) not null,"
                    + " QQ nvarchar(15) not null,"
                    + " Occupation nvarchar(20) not null,"
                    + " Culture nvarchar(20) not null,"
                    + " IncomeLevel nvarchar(50) not null,"
                    + " Address nvarchar(50) not null,"
                    + " TheCustomer nvarchar(20) not null,"
                    + " Remarks nvarchar(20) not null,"
                    + " CreatePeople nvarchar(100) not null,"
                    + " SetTime nvarchar(50) not null"
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + Name
                    + " add constraint PK_EssentialInformationId primary key (EssentialInformationId)";
                ConnPoll.Open();
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="ei"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Insert(EssentialInformation ei, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into EssentialInformation" + UserName
                + " (RemarksName,NickName,AccountNumber,The,Sex,Old,DateOfBirth,Telephone,QQ,Occupation,Culture,IncomeLevel,Address,TheCustomer,Remarks,CreatePeople,SetTime)"
                + " values (@RemarksName,@NickName,@AccountNumber,@The,@Sex,@Old,@DateOfBirth,@Telephone,@QQ,@Occupation,@Culture,@IncomeLevel,@Address,@TheCustomer,@Remarks,@CreatePeople,@SetTime)";

            SqlParameter[] pars = new SqlParameter[]
                    {
                        new SqlParameter("@RemarksName",ei.RemarksName),
                        new SqlParameter("@NickName",ei.NickName),
                        new SqlParameter("@AccountNumber",ei.AccountNumber),
                        new SqlParameter("@The",ei.The),
                        new SqlParameter("@Sex",ei.Sex),
                        new SqlParameter("@Old",ei.Old),
                        new SqlParameter("@DateOfBirth",ei.DateOfBirth),
                        new SqlParameter("@Telephone",ei.Telephone),
                        new SqlParameter("@QQ",ei.QQ),
                        new SqlParameter("@Occupation",ei.Occupation),
                        new SqlParameter("@Culture",ei.Culture),
                        new SqlParameter("@IncomeLevel",ei.IncomeLevel),
                        new SqlParameter("@Address",ei.Address),
                        new SqlParameter("@TheCustomer",ei.TheCustomer),
                        new SqlParameter("@Remarks",ei.Remarks),
                        new SqlParameter("@CreatePeople",ei.CreatePeople),
                        new SqlParameter("@SetTime",ei.SetTime)
                };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);

        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="ei"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Delete(EssentialInformation ei, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete EssentialInformation" + UserName + " where EssentialInformationId=@EssentialInformationId";
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, new SqlParameter("EssentialInformationId", ei.EssentialInformationId));
        }

        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="ei"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Set(EssentialInformation ei, string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update EssentialInformation" + UserName 
                + " set"
                + " RemarksName=@RemarksName,"
                + " NickName=@NickName,"
                + " AccountNumber=@AccountNumber,"
                + " The=@The,"
                + " Sex=@Sex,"
                + " Old=@Old,"
                + " DateOfBirth=@DateOfBirth,"
                + " Telephone=@Telephone,"
                + " QQ=@QQ,"
                + " Occupation=@Occupation,"
                + " Culture=@Culture,"
                + " IncomeLevel=@IncomeLevel,"
                + " Address=@Address,"
                + " TheCustomer=@TheCustomer,"
                + " Remarks=@Remarks,"
                + " CreatePeople=@CreatePeople,"
                + " SetTime=@SetTime"
                + " where NickName=@FriendName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@RemarksName",ei.RemarksName),
                new SqlParameter("@NickName",ei.NickName),
                new SqlParameter("@AccountNumber",ei.AccountNumber),
                new SqlParameter("@The",ei.The),
                new SqlParameter("@Sex",ei.Sex),
                new SqlParameter("@Old",ei.Old),
                new SqlParameter("@DateOfBirth",ei.DateOfBirth),
                new SqlParameter("@Telephone",ei.Telephone),
                new SqlParameter("@QQ",ei.QQ),
                new SqlParameter("@Occupation",ei.Occupation),
                new SqlParameter("@Culture",ei.Culture),
                new SqlParameter("@IncomeLevel",ei.IncomeLevel),
                new SqlParameter("@Address",ei.Address),
                new SqlParameter("@TheCustomer",ei.TheCustomer),
                new SqlParameter("@Remarks",ei.Remarks),
                new SqlParameter("@CreatePeople",ei.CreatePeople),
                new SqlParameter("@SetTime",ei.SetTime),
                new SqlParameter("@EssentialInformationId",ei.EssentialInformationId),
                new SqlParameter("@FriendName",FriendName),
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int SearchAll(string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select count(1) from EssentialInformation{0} where NickName = '{1}'", UserName, FriendName);
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text,sql));
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public EssentialInformation Show(string UserName, string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select * from EssentialInformation{0} where NickName = '{1}'", UserName, FriendName);
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            EssentialInformation ei = new EssentialInformation();
            while (dr.Read())
            {
                ei.AccountNumber = dr["AccountNumber"].ToString().Trim();
                ei.Address = dr["Address"].ToString().Trim();
                ei.CreatePeople = dr["CreatePeople"].ToString().Trim();
                ei.Culture = dr["Culture"].ToString().Trim();
                ei.DateOfBirth = Convert.ToDateTime(dr["DateOfBirth"]);
                ei.EssentialInformationId = Convert.ToInt32(dr["EssentialInformationId"]);
                ei.IncomeLevel = dr["IncomeLevel"].ToString().Trim();
                ei.NickName = dr["NickName"].ToString().Trim();
                ei.Occupation = dr["Occupation"].ToString().Trim();
                ei.Old = dr["Old"].ToString().Trim();
                ei.QQ = dr["QQ"].ToString().Trim();
                ei.Remarks = dr["Remarks"].ToString().Trim();
                ei.RemarksName = dr["RemarksName"].ToString().Trim();
                ei.SetTime = dr["SetTime"].ToString().Trim();
                ei.Sex = dr["Sex"].ToString().Trim();
                ei.Telephone = dr["Telephone"].ToString().Trim();
                ei.The = dr["The"].ToString().Trim();
                ei.TheCustomer = dr["TheCustomer"].ToString().Trim();
            }
            dr.Close();
            return ei;
        }
    }
}
